import os
import sqlite3

from CommonOperations.project_root_path import project_root_path


class ParseSQLite():
    root_path = project_root_path()
    default_db_path = os.path.join(root_path, "ImageRecords.sqlite")

    def __init__(self, db_path=None):
        if db_path is None:
            use_db_path = self.default_db_path
        else:
            use_db_path = db_path

        if not os.path.exists(use_db_path):
            self.connect_db = sqlite3.connect(use_db_path)
            self.cursor_db = self.connect_db.cursor()
            # 如果数据库不存在，则在项目主目录下创建本地数据库
            self.create_table()
        else:
            self.connect_db = sqlite3.connect(use_db_path)
            self.cursor_db = self.connect_db.cursor()

    def add_img_record(self, img_w, img_h, img_desc, img_time_stamp, img_type, img_save_path):
        # 插入一条记录
        insert_sql = f"""
        INSERT INTO image_history (img_width, img_height, img_desc, create_time, img_type, img_save_path) 
        VALUES ({img_w}, {img_h}, "{img_desc}", "{img_time_stamp}", "{img_type}", "{img_save_path}");
        """
        self.cursor_db.execute(insert_sql)
        self.connect_db.commit()

    def create_table(self):
        """
        初始化数据库及表
        :return:
        """
        create_table_sql = """
            CREATE TABLE image_history
            (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            img_width INTEGER NOT NULL,
            img_height INTEGER NOT NULL,
            img_desc TEXT,
            create_time TEXT,
            img_type TEXT,
            img_save_path TEXT NOT NULL);
            """
        self.cursor_db.execute(create_table_sql)
        self.connect_db.commit()

    def select_table_data(self, table_name, *table_columns, num=200, search_key=None, search_value=None):
        """
        查询对应表中，需要的字段内容
        :param table_name: 表名
        :param table_columns: 查询列
        :param num: 总结果数量
        :param search_key: 条件字段
        :param search_value: 条件字段匹配值
        :return:
        """
        if len(table_columns) >0:
            column_list = ', '.join(table_columns[0])
        else:
            column_list = "*"

        if search_key is None or search_value is None:
            select_table_data = f"""
            SELECT {column_list} FROM {table_name} ORDER BY id DESC LIMIT {num};
            """
        else:
            select_table_data = f"""
            SELECT {column_list} FROM {table_name} WHERE {search_key}={search_value} ORDER BY id DESC LIMIT {num};
            """

        select_result_origin = self.cursor_db.execute(select_table_data)
        select_result = []

        for i in select_result_origin:
            select_result.append(i)

        return select_result


    def __del__(self):
        """
        关闭数据库连接
        :return:
        """
        self.connect_db.close()

